package com.zenithsun.pic.picreview.dao.impl;

import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.springframework.stereotype.Repository;

import com.zenithsun.common.bean.ModelType;
import com.zenithsun.common.dao.NativeSQLDaoHibernate;
import com.zenithsun.common.review.model.Review;
import com.zenithsun.common.security.sql.SqlInjectionException;
import com.zenithsun.common.security.sql.SqlInjectionManager;
import com.zenithsun.pic.picreview.dao.PicReviewDAO;
import com.zenithsun.pic.picreview.model.PicReview;

@Repository("picReviewDao")
public class PicReviewDAOImpl extends NativeSQLDaoHibernate<PicReview> implements PicReviewDAO {

	@Override
	protected Class<PicReview> getEntityClass() {
		return PicReview.class;
	}
	@Override
	public Review getReviewByBus(String busId, String busMark) throws Exception {
		SQLQuery query=getSession().createSQLQuery("SELECT R.* FROM REVIEW R WHERE R.BUSID=? AND R.BUS_MARK=?");
		query.addEntity(Review.class);
		query.setParameter(0,busId);
		query.setParameter(1,busMark);
		List list=query.list();
		if(list!=null&&list.size()>0){
			return (Review)list.get(0);
		}
		return null;
	}
	/**
	 * 根据条件查询数量
	 * @param searchCondition
	 * @return int
	 * @throws Exception
	 */
	@Override
	public int countPicReview(PicReview searchCondition) throws Exception {
		// TODO Auto-generated method stub
		String SQL_REWIEW_COUNT = "SELECT COUNT(*) totalCount FROM PICSUBJECT P LEFT JOIN (SELECT V.state,V.ID reviewId,V.busId,DATE_FORMAT(V.create_time, '%Y-%m-%d') createTime FROM REVIEW V WHERE V.BUS_MARK='Pic') N ON P.ID = N.BUSID";
		SQLQuery query=getSession().createSQLQuery(getCondition(SQL_REWIEW_COUNT,searchCondition));
		return getCountBySQL(query);
	}
	
	/**
	 * 分页查询
	 * @param pageNo 页码
	 * @param limit  页行数
	 * @param searchCondition 查找参数
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<HashMap<String, String>> listPicReview(int pageNo, int limit,
			PicReview searchCondition) throws Exception {
		// TODO Auto-generated method stub
		String SQL_REWIEW_LIST = "SELECT P.*,N.state,N.createTime,N.revId,N.comments,(SELECT COUNT(D.ID) FROM PICDETAIL D WHERE PIC_ID=P.ID) picNum from (SELECT P1.*,P2.path coverPath FROM PICSUBJECT P1 LEFT JOIN PICDETAIL P2 ON  P1.cover_id=P2.ID) P LEFT JOIN (SELECT V.state,V.ID revId,V.busId,V.comments,DATE_FORMAT(V.create_time, '%Y-%m-%d') createTime FROM REVIEW V WHERE V.BUS_MARK='Pic') N ON P.ID = N.BUSID";
		SQLQuery query=getSession().createSQLQuery(getCondition(SQL_REWIEW_LIST,searchCondition));
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		//设置分页条件
		query.setFirstResult((pageNo - 1) * limit).setMaxResults(limit);
		List<HashMap<String, String>> list=query.list();
		return list;
	}
	
	private static String getCondition(String sql,PicReview searchCondition) throws SqlInjectionException{
		StringBuffer sb=new StringBuffer();
		sb.append(" WHERE 1=1 ");
		if(searchCondition.getState()>-1){
			sb.append(" AND N.STATE= ").append(searchCondition.getState());
		}
		if(StringUtils.isNotBlank(searchCondition.getName())){
			SqlInjectionManager.validateSqlParam(searchCondition.getName());
			sb.append(" AND P.NAME like '%").append(searchCondition.getName()).append("%'");
		}
		if(sb.length()==0){
			return "";
		}else{
			sb.insert(0, sql);
		}
			sb.append(" ORDER BY CREATETIME DESC");
		return sb.toString();
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public HashMap<String, String> findPicReview(String id)
			throws Exception {
		// TODO Auto-generated method stub
		HashMap<String, String> map = new HashMap<String, String>();
		String SQL_REVIEW = "SELECT P.*,N.state,N.createTime,N.revId,N.comments,(SELECT COUNT(D.ID) FROM PICDETAIL D WHERE PIC_ID=P.ID) picNum from (SELECT V.state,V.ID revId,V.busId,DATE_FORMAT(V.create_time, '%Y-%m-%d') createTime,V.comments FROM REVIEW V WHERE V.ID='"+id+"') N LEFT JOIN (SELECT P1.*,P2.path coverPath FROM PICSUBJECT P1 LEFT JOIN PICDETAIL P2 ON  P1.cover_id=P2.ID) P ON P.ID = N.BUSID";
		SQLQuery query=getSession().createSQLQuery(SQL_REVIEW);
		query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
		if(query.list().size() > 0)
		{
			map = (HashMap<String, String>)query.list().get(0);
		}
		return map;
	}
	
}
